package com.youlu.campus.service.data.impl;

import com.alibaba.fastjson.JSON;
import com.mongodb.client.result.UpdateResult;
import com.youlu.campus.base.exception.BusinessException;
import com.youlu.campus.common.utils.ExcelUtil;

import com.youlu.campus.common.utils.TimeUtils;
import com.youlu.campus.entity.*;
import com.youlu.campus.entity.VO.req.UserJoinActivityDataImport;
import com.youlu.campus.entity.VO.req.UserJoinActivityDataQueryVO;
import com.youlu.campus.entity.data.JoinActivity;
import com.youlu.campus.entity.data.UserJoinActivityData;
import com.youlu.campus.service.data.UserJoinActivityDataService;
import com.youlu.campus.service.file.FileExportTaskService;
import com.youlu.campus.service.file.FileService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.data.mongodb.core.query.Query;
import org.springframework.data.mongodb.core.query.Update;
import org.springframework.data.repository.support.PageableExecutionUtils;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Pattern;

@Slf4j
@Service
public class UserJoinActivityDataServiceImpl implements UserJoinActivityDataService {
    @Autowired
    private MongoTemplate mongoTemplate;
    @Autowired
    private FileExportTaskService fileExportTaskService;
    @Autowired
    private FileService fileService;

    @Value("${sys.file.path:/home/app/file}")
    private String filePath;

    private HashMap<String, JoinActivity> joinActivityHashMap = new HashMap<>();
    private HashMap<String, UniversityInfo> universityInfoHashMap = new HashMap<>();

    @Override
    public UserJoinActivityData create(UserJoinActivityData req) {
        req.setId(null);
        req.setCreatedTime(new Date());
        req = mongoTemplate.insert(req);
        return req;
    }

    @Override
    public boolean update(UserJoinActivityData u) {
        return false;
    }

    @Override
    public boolean delete(String id) {
        return false;
    }

    @Override
    public Map<String, Object> dataImport(byte[] inputStream, String fileName, String activityId) {
        log.info(":>>> 活动数据导入 fileName {}", fileName);
        Date date = new Date();
        try {
            Map<String, Object> result = new HashMap<>();
            List<UserJoinActivityDataImport> models = ExcelUtil.readData(new ByteArrayInputStream(inputStream), UserJoinActivityDataImport.class);
            result.put("allCount", models.size());
            int matchCount = 0;
            int notMatchCount = 0;
            List<Integer> notMatchRows = new ArrayList<>();
            List<UserJoinActivityData> datas = new ArrayList<>();
            if (!CollectionUtils.isEmpty(models)) {
                UserJoinActivityDataImport model = models.get(0);
                if (StringUtils.isNotBlank(model.getActivityName())) {
                    JoinActivity joinActivity = find(model.getActivityName());
                    if (Objects.isNull(joinActivity)) {
                        joinActivity = new JoinActivity();
                        joinActivity.setName(model.getActivityName());
                        joinActivity.setCreatedTime(new Date());
                        joinActivity.setFileName(fileName);
                        joinActivity.setNum(models.size());
                        joinActivity.setImportTime(new Date());
                        mongoTemplate.insert(joinActivity);
                        log.info(":>>> 开始新增:{} 活动", model.getActivityName());
                    }
                }
            }

            for (int i = 0; i < models.size(); i++) {
                log.info(":>>> 开始处理第:{} 行", i);
                UserJoinActivityDataImport model = models.get(i);
                log.info(":>>> 手机号码 {} 姓名:{},活动名称:{},批次号:{}", model.getMobile(), model.getName(), model.getActivityName(), model.getBatchNo());
                if (StringUtils.isBlank(model.getMobile()) || StringUtils.isBlank(model.getName()) || StringUtils.isBlank(model.getActivityName())
                        || StringUtils.isBlank(model.getBatchNo()) || StringUtils.isBlank(model.getBizType())) {
                    log.error(":>>> 导入参数错误:手机号和电话为为空、活动名称、批次号、业务类型为空");
                    continue;
                }
                UserJoinActivityData userJoinActivityData = new UserJoinActivityData();
                BeanUtils.copyProperties(model, userJoinActivityData);
                userJoinActivityData.setId(null);
                userJoinActivityData.setFileName(fileName);
                userJoinActivityData.setCreatedTime(date);
                UniversityInfo universityInfo = getUniversityInfo(model.getUniversityName());
                if (Objects.nonNull(universityInfo)) {
                    userJoinActivityData.setUniversityId(universityInfo.getId());
                    userJoinActivityData.setUniversityName(universityInfo.getName());
                    userJoinActivityData.setProvince(universityInfo.getProvince());
                    userJoinActivityData.setCity(universityInfo.getCity());
                }
                datas.add(userJoinActivityData);
                if (datas.size() == 1000) {
                    mongoTemplate.insertAll(datas);
                    datas.clear();
                    log.info(":>>> 插入1000条数据,清空列表:{}", datas.size());
                }

            }
            if (datas.size() > 0) {
                mongoTemplate.insertAll(datas);
                log.info(":>>> 插入:{} 条数据", datas.size());
                datas.clear();
            }
            result.put("matchCount", matchCount);
            result.put("notMatchCount", notMatchCount);
            result.put("notMatchRows", notMatchRows);
            return result;
        } catch (Exception e) {
            log.error(":>>> 活动数据导入 error fileName {}", fileName, e);
            return null;
        }
    }

    @Override
    public Page<UserJoinActivityData> list(UserJoinActivityDataQueryVO req) {
        log.info(":>>> 开始获取活动:{}", JSON.toJSONString(req));
        if (Objects.isNull(req)) {
            log.error(":>>> 参数错误");
            throw new BusinessException("参数错误");
        }
        Query query = this.getQuery(req);
        long count = mongoTemplate.count(query, UserJoinActivityData.class);
        PageRequest pageRequest = PageRequest.of(req.getPage() - 1, req.getPageSize());
//        query.with(new Sort(Sort.Direction.DESC, "createdTime"));
        Sort sort=Sort.by(Sort.Direction.DESC, "createdTime");
        query.with(sort);
        List<UserJoinActivityData> orderPOS = mongoTemplate.find(query.with(pageRequest), UserJoinActivityData.class);
        return PageableExecutionUtils.getPage(orderPOS, pageRequest, () -> count);
    }

    @Override
    public String export(UserJoinActivityDataQueryVO req) {
        log.info(":>>> 开始订单导出:{}", JSON.toJSONString(req));
        Query query = this.getQuery(req);
        Long count = mongoTemplate.count(query, UserJoinActivityData.class);
        log.info(":>>> 订单总量:{}", count);
        FileExportTask fileExportTask = new FileExportTask();
        fileExportTask.setToken(req.getOptUserName());
        fileExportTask.setBizType("order");
        fileExportTask.setStatus(0);
        fileExportTask.setOptUser(req.getOptUserName());
        fileExportTask.setOptUserId(req.getUserId());
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String dateStr = sdf.format(new Date());
        String fileName = "全数据统计-" + "-" + dateStr;
        String f = fileName + ".xlsx";
        fileExportTask.setFileName(f);
        fileExportTask.setNum(count.intValue());
        fileExportTask.setDesc("全数据统计--" + dateStr);
        String re = fileExportTaskService.create(fileExportTask);
        this.exportAllDatas(query, f, re, req.getStartPage(), req.getEndPage(), req.getSheetSize());
        return re;
    }

    @Override
    public JoinActivity find(String name) {
        log.info(":>>> 开始查找活动:{}", name);
        if (joinActivityHashMap.containsKey(name)) {
            return joinActivityHashMap.get(name);
        }
        Query query = new Query().addCriteria(Criteria.where("name").is(name));
        JoinActivity joinActivity = mongoTemplate.findOne(query, JoinActivity.class);
        if (Objects.nonNull(joinActivity)) {
            joinActivityHashMap.put(name, joinActivity);
        }
        return joinActivity;
    }

    private Query getQuery(UserJoinActivityDataQueryVO req) {
        Query query = new Query();
        if (StringUtils.isNotBlank(req.getActivityId())) {
            query.addCriteria(Criteria.where("activityId").is(req.getActivityId()));
        }
        if (StringUtils.isNotBlank(req.getUniversityId())) {
            query.addCriteria(Criteria.where("universityId").is(req.getUniversityId()));
        }
        if (StringUtils.isNotBlank(req.getActivityName())) {
            Pattern pattern = Pattern.compile("^.*" + req.getActivityName() + ".*$", Pattern.CASE_INSENSITIVE);
            query.addCriteria(Criteria.where("activityName").regex(pattern));
        }
        if (StringUtils.isNotBlank(req.getName())) {
            Pattern pattern = Pattern.compile("^.*" + req.getName() + ".*$", Pattern.CASE_INSENSITIVE);
            query.addCriteria(Criteria.where("name").regex(pattern));
        }
        if (StringUtils.isNotBlank(req.getMobile())) {
            Pattern pattern = Pattern.compile("^.*" + req.getMobile() + ".*$", Pattern.CASE_INSENSITIVE);
            query.addCriteria(Criteria.where("mobile").regex(pattern));
        }
        return query;
    }

        private void exportAllDatas(Query query, String f, String exportTaskId, Integer startQ, Integer endQ, Integer sheetSize) {
        int counter = 0;
        int c = 0;
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        try {
            int rowNum = 1;
            int sheetNum = 1;
            //创建一个Excel表单,参数为sheet的名字
            SXSSFSheet sheet = workbook.createSheet("sheet" + "-" + sheetNum);
            setActivityUserExcelTitle(workbook, sheet);
            //创建表头
            Long count = mongoTemplate.count(query, UserJoinActivityData.class);
            Integer limit = 50000;
            SXSSFRow row = null;
            for (int i = startQ; i < endQ; i++) {
                log.info(":>>> 开始处理第:{} 页", i);
                query.limit(limit);
                query.skip(i * limit);
                List<UserJoinActivityData> contents = mongoTemplate.find(query, UserJoinActivityData.class);
                for (UserJoinActivityData content : contents) {
                    log.info(":>>> 计数器:{}", counter);
                    if (c != 0 && c % sheetSize == 0) {
                        //超过60000就自动创建一个sheet
                        sheetNum++;
                        sheet = workbook.createSheet("sheet-" + sheetNum);
                        setActivityUserExcelTitle(workbook, sheet);
//                            setCaOrderExcelTitle(workbook, sheet);
                        //创建表头
                        //新增数据行，并且设置单元格数据
                        rowNum = 1;

                        log.info(":>>> 操作100000行,创建新sheet");
                    }
                    row = sheet.createRow(rowNum);
                    if (StringUtils.isNotBlank(content.getName())) {
                        row.createCell(0).setCellValue(content.getName());
                    }
                    if (StringUtils.isNotBlank(content.getMobile())) {
                        row.createCell(1).setCellValue(content.getMobile());
                    }
                    if (StringUtils.isNotBlank(content.getQq())) {
                        row.createCell(2).setCellValue(content.getQq());
                    }
                    if (StringUtils.isNotBlank(content.getUniversityName())) {
                        row.createCell(3).setCellValue(content.getUniversityName());
                    }
                    if (StringUtils.isNotBlank(content.getCaName())) {
                        row.createCell(4).setCellValue(content.getCaName());
                    }

                    if (StringUtils.isNotBlank(content.getCaNo())) {
                        row.createCell(5).setCellValue(content.getCaNo());
                    }
                    if (StringUtils.isNotBlank(content.getCaLevel())) {
                        row.createCell(6).setCellValue(content.getCaLevel());
                    }
                    if (StringUtils.isNotBlank(content.getActivityName())) {
                        row.createCell(7).setCellValue(content.getActivityName());
                    }
                    if (StringUtils.isNotBlank(content.getRecieveCaTime())) {
                        row.createCell(8).setCellValue(content.getRecieveCaTime());
                    }
                    if (StringUtils.isNotBlank(content.getCaOrg())) {
                        row.createCell(9).setCellValue(content.getCaOrg());
                    }
                    if (StringUtils.isNotBlank(content.getCity())) {
                        row.createCell(10).setCellValue(content.getCity());
                    }
                    if (StringUtils.isNotBlank(content.getProvince())) {
                        row.createCell(11).setCellValue(content.getProvince());
                    }
                    if (StringUtils.isNotBlank(content.getUniversityName())) {
                        UniversityInfo universityInfo = this.getUniversityInfo(content.getUniversityName());
                        if (Objects.nonNull(universityInfo)) {
                            if (StringUtils.isNotBlank(universityInfo.getCity())) {
                                row.createCell(12).setCellValue(universityInfo.getCity());
                            }
                            if (StringUtils.isNotBlank(universityInfo.getProvince())) {
                                row.createCell(13).setCellValue(universityInfo.getProvince());
                            }
                            if (StringUtils.isNotBlank(universityInfo.getType())) {
                                row.createCell(14).setCellValue(universityInfo.getType());
                            }
                            if (Objects.nonNull(universityInfo.getUserCount())) {
                                row.createCell(15).setCellValue(universityInfo.getUserCount());
                            }
                            if (StringUtils.isNotBlank(universityInfo.getLevel())) {
                                row.createCell(16).setCellValue(universityInfo.getLevel());
                            }
                            if (Objects.nonNull(universityInfo.getMerged())) {
                                row.createCell(17).setCellValue(universityInfo.getMerged());
                            }
                            if (Objects.nonNull(universityInfo.getEstablishedTime())) {
                                String dateNowStr = TimeUtils.parse(universityInfo.getEstablishedTime(), "yyyy-MM-dd HH:mm:ss");
                                row.createCell(18).setCellValue(dateNowStr);
                            }
                            if (Objects.nonNull(universityInfo.getEmploymentRate())) {
                                row.createCell(19).setCellValue(universityInfo.getEmploymentRate());
                            }

                        }
                    }
                    if (Objects.nonNull(content.getInviteNum())) {
                        row.createCell(20).setCellValue(content.getInviteNum());
                    }
                    if (Objects.nonNull(content.getPv())) {
                        row.createCell(21).setCellValue(content.getPv());
                    }
                    if (Objects.nonNull(content.getPlaceOrderTime())) {
                        String dateNowStr = TimeUtils.parse(content.getPlaceOrderTime(), "yyyy-MM-dd HH:mm:ss");
                        row.createCell(22).setCellValue(dateNowStr);
                    }
                    if (Objects.nonNull(content.getPayFee())) {
                        row.createCell(23).setCellValue(content.getPayFee());
                    }
                    if (Objects.nonNull(content.getExpressFee())) {
                        row.createCell(24).setCellValue(content.getExpressFee());
                    }
                    if (Objects.nonNull(content.getTags())) {
                        row.createCell(26).setCellValue(JSON.toJSONString(content.getTags()));
                    }
                    if (Objects.nonNull(content.getComplainted())) {
                        row.createCell(27).setCellValue(content.getComplainted());
                    }
                    if (Objects.nonNull(content.getComplaintNum())) {
                        row.createCell(28).setCellValue(content.getComplaintNum());
                    }
                    if (Objects.nonNull(content.getComplaintChannel())) {
                        row.createCell(29).setCellValue(content.getComplaintChannel());
                    }
                    if (Objects.nonNull(content.getRefund())) {
                        row.createCell(30).setCellValue(content.getRefund());
                    }
                    if (Objects.nonNull(content.getRefundTime())) {
                        String dateNowStr = TimeUtils.parse(content.getRefundTime(), "yyyy-MM-dd HH:mm:ss");
                        row.createCell(31).setCellValue(dateNowStr);
                    }
                    if (Objects.nonNull(content.getRefundFee())) {
                        row.createCell(32).setCellValue(content.getRefundFee());
                    }
                    if (Objects.nonNull(content.getGrade())) {
                        row.createCell(33).setCellValue(content.getGrade());
                    }
                    if (Objects.nonNull(content.getMajor())) {
                        row.createCell(34).setCellValue(content.getMajor());
                    }
                    if (Objects.nonNull(content.getDiscipline())) {
                        row.createCell(35).setCellValue(content.getDiscipline());
                    }
                    if (Objects.nonNull(content.getMedia())) {
                        row.createCell(36).setCellValue(content.getMedia());
                    }
                    if (Objects.nonNull(content.getGender())) {
                        row.createCell(37).setCellValue(content.getGender());
                    }
                    if (Objects.nonNull(content.getQaNum())) {
                        row.createCell(38).setCellValue(content.getQaNum());
                    }
                    if (Objects.nonNull(content.getActivtyNum())) {
                        row.createCell(39).setCellValue(content.getActivtyNum());
                    }
                    if (Objects.nonNull(content.getScore())) {
                        row.createCell(40).setCellValue(content.getScore());
                    } else if (Objects.nonNull(content.getQaScore1())) {
                        row.createCell(40).setCellValue(content.getQaScore1());
                    }
                    if (Objects.nonNull(content.getQaScore2())) {
                        row.createCell(41).setCellValue(content.getQaScore2());
                    }
                    if (Objects.nonNull(content.getQaScore3())) {
                        row.createCell(42).setCellValue(content.getQaScore3());
                    }
                    if (Objects.nonNull(content.getBizType())) {
                        row.createCell(43).setCellValue(content.getBizType());
                    }
                    if (Objects.nonNull(content.getDevice())) {
                        row.createCell(44).setCellValue(content.getDevice());
                    }
                    if (Objects.nonNull(content.getOs())) {
                        row.createCell(45).setCellValue(content.getOs());
                    }
                    if (Objects.nonNull(content.getBrowser())) {
                        row.createCell(46).setCellValue(content.getBrowser());
                    }
                    if (Objects.nonNull(content.getZone())) {
                        row.createCell(47).setCellValue(content.getZone());
                    }
                    if (Objects.nonNull(content.getIp())) {
                        row.createCell(48).setCellValue(content.getIp());
                    }
                    if (Objects.nonNull(content.getCaEdition())) {
                        row.createCell(49).setCellValue(content.getCaEdition());
                    }
                    if (Objects.nonNull(content.getCaPrice())) {
                        row.createCell(50).setCellValue(content.getCaPrice());
                    }
                    if (Objects.nonNull(content.getMedalType())) {
                        row.createCell(51).setCellValue(content.getMedalType());
                    }
                    if (Objects.nonNull(content.getMedalPrice())) {
                        row.createCell(52).setCellValue(content.getMedalPrice());
                    }
                    if (Objects.nonNull(content.getOrderStatus())) {
                        row.createCell(54).setCellValue(content.getOrderStatus());
                    }
                    if (Objects.nonNull(content.getPayTime())) {
                        row.createCell(55).setCellValue(content.getPayTime());
                    }

                    if (Objects.nonNull(content.getPayWay())) {
                        row.createCell(56).setCellValue(content.getPayWay());
                    }
                    if (Objects.nonNull(content.getIsWangKa())) {
                        row.createCell(57).setCellValue(content.getIsWangKa());
                    }
                    if (Objects.nonNull(content.getWangkaType())) {
                        row.createCell(58).setCellValue(content.getWangkaType());
                    }
                    if (Objects.nonNull(content.getWangkaShenyu())) {
                        row.createCell(59).setCellValue(content.getWangkaShenyu());
                    }
                    if (Objects.nonNull(content.getRefundFee())) {
                        row.createCell(60).setCellValue(content.getRefundFee());
                    }
                    if (Objects.nonNull(content.getRefundTime())) {
                        row.createCell(61).setCellValue(content.getRefundTime());
                    }
                    if (Objects.nonNull(content.getActivtyNum())) {
                        row.createCell(62).setCellValue(content.getActivtyNum());
                    }
                    if (Objects.nonNull(content.getLogisticsCompany())) {
                        row.createCell(63).setCellValue(content.getLogisticsCompany());
                    }
                    if (Objects.nonNull(content.getLogisticsNo())) {
                        row.createCell(64).setCellValue(content.getLogisticsNo());
                    }
                    if (Objects.nonNull(content.getTransactionId())) {
                        row.createCell(65).setCellValue(content.getTransactionId());
                    }
                    if (Objects.nonNull(content.getPlatform())) {
                        row.createCell(66).setCellValue(content.getPlatform());
                    }
                    rowNum++;
                    counter++;
                    c++;
                }

            }
            //将excel写入到输出流中
            log.info(":>>> flush:{} 行", rowNum);
            OutputStream outputStream = new FileOutputStream(filePath + f);
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
            log.info(":>>> 写文件到本地然后读:{}", f);
            File file = new File(filePath + f);
            InputStream in = new FileInputStream(file);
            String url = fileService.uploadFile(in, f);
            Query queryTask = new Query().addCriteria(Criteria.where("id").is(exportTaskId));
            Update update = new Update();
            update.set("url", url);
            update.set("status", 1);
            update.set("updatedTime", new Date());
            UpdateResult updateResult = mongoTemplate.updateFirst(queryTask, update, FileExportTask.class);
            log.info(":>>> 更新任务:{},状态:{}", exportTaskId, updateResult.getModifiedCount() > 0 ? true : false);
            in.close();
            log.info(":>>> 全量数据导出,关闭文件流");
        } catch (Exception e) {
            log.error(":>>> 全量数据导出错误:{}", e);
        }

    }

    private void setActivityUserExcelTitle(SXSSFWorkbook workbook, SXSSFSheet sheet) {
        SXSSFRow row = sheet.createRow(0);
        //设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
        sheet.setColumnWidth(0, 10 * 256);
        sheet.setColumnWidth(1, 10 * 256);
        sheet.setColumnWidth(2, 10 * 256);
        sheet.setColumnWidth(3, 10 * 256);

        SXSSFCell cell;
        cell = row.createCell(0);
        cell.setCellValue("姓名");


        cell = row.createCell(1);
        cell.setCellValue("电话");


        cell = row.createCell(2);
        cell.setCellValue("QQ号");


        cell = row.createCell(3);
        cell.setCellValue("大学名");


        cell = row.createCell(4);
        cell.setCellValue("证书名");


        cell = row.createCell(5);
        cell.setCellValue("证书编号");


        cell = row.createCell(6);
        cell.setCellValue("证书等级");


        cell = row.createCell(7);
        cell.setCellValue("活动名");


        cell = row.createCell(8);
        cell.setCellValue("发证时间");


        cell = row.createCell(9);
        cell.setCellValue("发证机构");


        cell = row.createCell(10);
        cell.setCellValue("城市");


        cell = row.createCell(11);
        cell.setCellValue("省");

        cell = row.createCell(12);
        cell.setCellValue("城市");

        cell = row.createCell(13);
        cell.setCellValue("省");

        cell = row.createCell(14);
        cell.setCellValue("学校类型");

        cell = row.createCell(15);
        cell.setCellValue("学校人数");

        cell = row.createCell(16);
        cell.setCellValue("学校级别");

        cell = row.createCell(17);
        cell.setCellValue("是否合并");

        cell = row.createCell(18);
        cell.setCellValue("成立时间");

        cell = row.createCell(19);
        cell.setCellValue("就业率");

        cell = row.createCell(20);
        cell.setCellValue("邀请人数");

        cell = row.createCell(21);
        cell.setCellValue("PV");

        cell = row.createCell(22);
        cell.setCellValue("下单时间");

        cell = row.createCell(23);
        cell.setCellValue("支付金额");

        cell = row.createCell(24);
        cell.setCellValue("邮费");

        cell = row.createCell(26);
        cell.setCellValue("标签");

        cell = row.createCell(27);
        cell.setCellValue("是否投诉");

        cell = row.createCell(28);
        cell.setCellValue("投诉次数");

        cell = row.createCell(29);
        cell.setCellValue("投诉渠道");

        cell = row.createCell(30);
        cell.setCellValue("是否退款");

        cell = row.createCell(31);
        cell.setCellValue("退款时间");

        cell = row.createCell(32);
        cell.setCellValue("退款金额");

        cell = row.createCell(33);
        cell.setCellValue("年级");

        cell = row.createCell(34);
        cell.setCellValue("专业");

        cell = row.createCell(35);
        cell.setCellValue("学科");

        cell = row.createCell(36);
        cell.setCellValue("投放媒体");

        cell = row.createCell(37);
        cell.setCellValue("性别");

        cell = row.createCell(38);
        cell.setCellValue("答题次数");

        cell = row.createCell(39);
        cell.setCellValue("参加活动次数");

        cell = row.createCell(40);
        cell.setCellValue("答题分数1");
        cell = row.createCell(41);
        cell.setCellValue("答题分数2");
        cell = row.createCell(42);
        cell.setCellValue("答题分数3");

        cell = row.createCell(43);
        cell.setCellValue("业务类型,报名,收款");

        cell = row.createCell(44);
        cell.setCellValue("填写设备");
        cell = row.createCell(45);
        cell.setCellValue("操作系统");
        cell = row.createCell(46);
        cell.setCellValue("浏览器");
        cell = row.createCell(47);
        cell.setCellValue("填写地区");
        cell = row.createCell(48);
        cell.setCellValue("IP地址");
        cell = row.createCell(49);
        cell.setCellValue("证书版本");
        cell = row.createCell(50);
        cell.setCellValue("证书价格");
        cell = row.createCell(51);
        cell.setCellValue("勋章类型");
        cell = row.createCell(52);
        cell.setCellValue("勋章价格");
        cell = row.createCell(53);
        cell.setCellValue("订单状态");
        cell = row.createCell(54);
        cell.setCellValue("支付时间");
        cell = row.createCell(55);
        cell.setCellValue("支付方式");
        cell = row.createCell(56);
        cell.setCellValue("是否王卡");
        cell = row.createCell(57);
        cell.setCellValue("王卡类型");
        cell = row.createCell(58);
        cell.setCellValue("王卡剩余次数");
        cell = row.createCell(59);
        cell.setCellValue("参加活动次数");
        cell = row.createCell(60);
        cell.setCellValue("物流公司");
        cell = row.createCell(61);
        cell.setCellValue("物流编号");
        cell = row.createCell(62);
        cell.setCellValue("微信交易号");
        cell = row.createCell(63);
        cell.setCellValue("平台");
        cell = row.createCell(64);
        cell.setCellValue("邮费");

    }

    private UniversityInfo getUniversityInfo(String name) {
        if (StringUtils.isBlank(name)) {
            log.error(":>>> 大学信息名字为空:{}", name);
            return null;
        }
        if (universityInfoHashMap.containsKey(name)) {
            return universityInfoHashMap.get(name);
        }
        Query queryA = new Query().addCriteria(Criteria.where("name").is(name));
        UniversityInfo universityInfo = mongoTemplate.findOne(queryA, UniversityInfo.class);
        if (Objects.nonNull(universityInfo)) {
            universityInfoHashMap.put(name, universityInfo);
        }
        return universityInfo;
    }

}
